In a database management system (DBMS), data is stored in one or more data containers. Each container contains records. The data within each record is organized into one or more fields. In relational database management systems, the data containers are referred to as tables, the records are referred to as rows, and the fields are referred to as columns. In object-oriented databases, the data containers are referred to as object classes, the records are referred to as objects, and the fields are referred to as attributes. Other database architectures may use other terminology.
Database management systems retrieve information in response to receiving queries that specify the information to retrieve. In order for a database management system to understand the query, the query should conform to a database language recognized by the database management system, such as the Structured Query Language (SQL).
In an OLAP (on-line analytical processing) environment or a data warehousing environment, data is often organized into a star schema. A star schema is distinguished by the presence of one or more relatively large tables and several relatively smaller tables. Rather than duplicating the information contained in the smaller tables, the large tables contain references (foreign key values) to rows stored in the smaller tables. The larger tables within a star schema are referred to as “fact tables,” while the smaller tables are referred to as “dimension tables.” Different dimension tables represent different dimensions. Typically, each dimension has “levels” which correspond to columns of the dimension table, which are organized in a hierarchical manner. The items in the levels at the top of the hierarchy typically contain (or could contain) multiple instances of items from levels further below in the hierarchy. For example, a TIME dimension might consist of the levels year, quarter, month, and day, corresponding to columns of the TIME table. The hierarchical organization is such that years consist of quarters, quarters consist of months, and months consist of days.
Records in a fact table usually refer to records in dimension tables using corresponding foreign key values. A particular foreign key value in a single column of a fact table can refer to a corresponding multi-column record (associated with a same key value) in a dimension table. As a result, the fact table can be expressed in a more condensed manner; the fact table can contain fewer columns. The columns of a fact table will often be columns that contain either (a) foreign key values that correspond to key values in dimension tables (often, the fact table will contain one such column for each separate dimension table—e.g., TIME, REGION—to which the fact table refers) or (b) scalar (non-enumerated) “measure” values, such as, for example, a sales amount. The columns of a dimension table will often be columns that contain either (a) key values to which the foreign key values in a particular column of the fact table correspond or (b) enumerated values, such as, for example, actual displayable values for year (e.g., “1999”), quarter (e.g., “Q1”), month (e.g., “JAN”), region (e.g., “WEST”), and state (e.g., “CA”). In a dimension table, each different combination of enumerated values in that dimension table is associated with key value that is unique within that dimension table, so that the fact table can refer to a specific combination of values represented in the dimension table by referring to that associated key value. For example, a TIME dimension table might contain a row for the (year, quarter, month) combination (“1999,” “Q1,” “JAN”), and this row might be associated with the key value “1.” In the fact table, there may be one or more rows in which the value of a “TIME” column contains the foreign key value “1” to refer to the specific combination (“1999,” “Q1,” “JAN”) in the TIME dimension table.